﻿using PRO.Common;
using PRO.Data;
using PRO.ORM;
using PRO.Permission.Entity;
using PRO.PermissionProvider;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;


namespace PRO.PermissionSQL
{
    public partial class PermissionManager :IPermissionProvider
    {
        public int AddPermissionGroup(PermissionGroup g)
        {

            SqlHelper sqlHelper = new SqlInsert();
            sqlHelper.AddEntity(g,"PermissionItems");
            return sqlHelper.Run().ToString().ToInt(0);
        }
        public bool EditPermissionGroup(PermissionGroup g)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("update PermissionGroup set ");
            //strSql.Append("GroupTitle=@GroupTitle,");
            //strSql.Append("GroupNote=@GroupNote");
            //strSql.Append(" where Id=@Id");

            //DbParameter[] parameters = {
            //        DbHelper.MakeInParam("@Id", (DbType)SqlDbType.Int,4,g.ID),
            //        DbHelper.MakeInParam("@GroupTitle", (DbType)SqlDbType.NVarChar,100,g.GroupTitle),
            //        DbHelper.MakeInParam("@GroupNote", (DbType)SqlDbType.NVarChar,250,g.GroupNote)};

            //return Convert.ToBoolean(DbHelper.ExecuteNonQuery(CommandType.Text,strSql.ToString(),parameters));
            SqlHelper sqlHelper = new SqlUpdate();
            sqlHelper.AddEntity(g);
            return sqlHelper.Run().ToString().ToBoolean(false);
        }
        public bool DelPermissionGroup(int id)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("delete from PermissionGroup ");
            //strSql.Append(" where Id=@Id");

            //DbParameter[] parameters = {
            //        DbHelper.MakeInParam("@Id",(DbType)SqlDbType.BigInt,4,id)
            //                           };
            //return Convert.ToBoolean(DbHelper.ExecuteNonQuery(CommandType.Text,strSql.ToString(),parameters));

            SqlHelper sq = new SqlDelete("PermissionGroup");
            sq.AddWhereAnd("Id",id,SqlDbType.Int,4,"=");
            return Convert.ToBoolean(sq.Run());


        }

        public bool PermissionExist(string powername)
        {
            string sql = "select count(id) from powerinfo where PowerName=@PowerName";
            DbParameter[] parameters = { 
					DbHelper.MakeInParam("@PowerName", (DbType)SqlDbType.NVarChar,100,powername)
                                          };
            if(Utils.StrToInt(DbHelper.ExecuteScalar(CommandType.Text,sql,parameters),-1) > 0)
                return true;
            return false;
        }


        public int AddPermission(PermissionItem g)
        {
            if(PermissionExist(g.Permission))//检查是否存在该 PermissionItem
                return -1;

            SqlHelper sq = new SqlInsert();
            sq.AddEntity(g);
            return sq.Run().ToString().ToInt(0);
        }

        public bool EditPermission(PermissionItem g)
        {
            SqlHelper sq = new SqlInsert();
            sq.AddEntity(g);
            return sq.Run().ToString().ToBoolean(false);
        }

        public bool DelPermission(int id)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("delete from PermissionItem ");
            //strSql.Append(" where Id=@Id");

            //DbParameter[] parameters = {
            //        DbHelper.MakeInParam("@Id",(DbType)SqlDbType.BigInt,4,id)
            //                           };
            //return Convert.ToBoolean(DbHelper.ExecuteNonQuery(CommandType.Text,strSql.ToString(),parameters));

            SqlHelper sq = new SqlDelete("PermissionItem");
            sq.AddWhereAnd("Id",id,SqlDbType.Int,4,"=");
            return Convert.ToBoolean(sq.Run());
        }

        /// <summary>
        /// 获取指定分组的所有权限信息
        /// </summary>
        /// <param name="groupid"></param>
        /// <returns></returns>
        public DataTable GetPermissionsByGroup(int groupid)
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select  Id,Permission,GroupID,PermissionNote from PermissionItem ");
            //strSql.Append(" where GroupID=@GroupID");

            //DbParameter[] parameters = {
            //        DbHelper.MakeInParam("@GroupId", (DbType)SqlDbType.Int,4,groupid)
            //                           };

            //return DbHelper.ExecuteDataset(CommandType.Text,strSql.ToString(),parameters).Tables[0];

            SqlHelper sq = new SqlSelect();
            sq.AddEntity(new PermissionItem());
            sq.AddWhereAnd("GroupID",groupid,SqlDbType.Int,4,"=");
            return (DataTable)sq.Run();

        }
        /// <summary>
        /// 获取所有权限信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetPermissions()
        {
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select  top 1 Id,Permission,GroupID,PermissionNote from PermissionItem ");

            //return DbHelper.ExecuteDataset(CommandType.Text,strSql.ToString()).Tables[0];

            SqlHelper sq = new SqlSelect();
            sq.AddEntity(new PermissionItem());
            return (DataTable)sq.Run();
        }

        /// <summary>
        /// 获取所有权限分组
        /// </summary>
        /// <returns></returns>
        public DataTable GetPermissionGroup()
        {
            //    StringBuilder strSql = new StringBuilder();
            //    strSql.Append("select id,GroupTitle,GroupNote from PermissionGroup ");
            //    return DbHelper.ExecuteDataset(CommandType.Text,strSql.ToString()).Tables[0];
            SqlHelper sq = new SqlSelect();
            sq.AddEntity(new PermissionGroup());
            return (DataTable)sq.Run();
        }

        public bool SetPermission(int userid,string permissions)
        {
            string sql = @" if not exists(select id from UserPermissions where userid=@userid)
	                            insert UserPermissions (UserID,[Permissions]) values(@userid,@Permissions)
                            else
	                            update UserPermissions set [Permissions]=@Permissions where userid=@userid";
            DbParameter[] parameters = {
					                    DbHelper.MakeInParam("@userid", (DbType)SqlDbType.Int,4,userid),
                                        DbHelper.MakeInParam("@Permissions", (DbType)SqlDbType.NVarChar,1000,permissions)
                                       };
            return Convert.ToBoolean(DbHelper.ExecuteNonQuery(CommandType.Text,sql,parameters));
        }

        public bool CheckPermissionDataBase()
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"begin transaction 
                        declare @errorSum int

                        IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PermissionItem]') AND type in (N'U'))
                        BEGIN
                        CREATE TABLE [dbo].[PermissionItem](
	                        [Id] [int] IDENTITY(1,1) NOT NULL,
	                        [Permission] [nvarchar](50) NOT NULL,
	                        [GroupID] [int] NOT NULL,
	                        [PermissionNote] [nvarchar](250) NULL,
                         CONSTRAINT [PK_PermissionItem] PRIMARY KEY CLUSTERED 
                        (
	                        [Id] ASC
                        )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
                        ) ON [PRIMARY]
                        END
	                    set @errorSum=@errorSum+@@error

                        IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PermissionItem]') AND name = N'PK_GroupID')
                        CREATE NONCLUSTERED INDEX [PK_GroupID] ON [dbo].[PermissionItem] 
                        (
	                        [GroupID] ASC
                        )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
                        set @errorSum=@errorSum+@@error

                        IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[PermissionItem]') AND name = N'PK_Permission')
                        CREATE NONCLUSTERED INDEX [PK_Permission] ON [dbo].[PermissionItem] 
                        (
	                        [Permission] ASC
                        )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
                        set @errorSum=@errorSum+@@error

                        IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PermissionGroup]') AND type in (N'U'))
                        BEGIN
                        CREATE TABLE [dbo].[PermissionGroup](
	                        [id] [int] IDENTITY(1,1) NOT NULL,
	                        [GroupTitle] [nvarchar](100) NOT NULL,
	                        [GroupNote] [nvarchar](250) NULL,
                         CONSTRAINT [PK_PermissionGroup] PRIMARY KEY CLUSTERED 
                        (
	                        [id] ASC
                        )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
                        ) ON [PRIMARY]
                        END
	                    set @errorSum=@errorSum+@@error

                        IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UserPermissions]') AND type in (N'U'))
                        BEGIN
                        CREATE TABLE [dbo].[UserPermissions](
	                        [id] [int] IDENTITY(1,1) NOT NULL,
	                        [UserID] [int] NOT NULL,
	                        [Permissions] [nvarchar](1000) NULL,
                         CONSTRAINT [PK_UserPermissions] PRIMARY KEY CLUSTERED 
                        (
	                        [id] ASC
                        )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
                        ) ON [PRIMARY]
                        END
	                    set @errorSum=@errorSum+@@error


                        IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[UserPermissions]') AND name = N'PK_UserID')
                        CREATE NONCLUSTERED INDEX [PK_UserID] ON [dbo].[UserPermissions] 
                        (
	                        [UserID] ASC
                        )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
                        set @errorSum=@errorSum+@@error

                        IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[IsAuthorized]') AND type in (N'P', N'PC'))
						BEGIN
						EXEC dbo.sp_executesql @statement = N'
						CREATE procedure [dbo].[IsAuthorized](@userid int,@permission nvarchar(100))
						as
						select count(id) from UserPermissions where userid=@userid and  
						 charindex((select '',''+cast(id as nvarchar)+'','' from PermissionItem where Permission=@permission),'',''+[Permissions]+'','')>0 ' 
						END
	                    set @errorSum=@errorSum+@@error

                        if @errorSum>0
                            begin
                                rollback transaction
                            end
                           else
                            begin
                                commit transaction
                            end
");
            return Convert.ToBoolean(DbHelper.ExecuteNonQuery(CommandType.Text,sql.ToString()));
        }
        public bool IsAuthorized(int userid,string permission)
        {
            DbParameter[] parameters = {
                                        DbHelper.MakeInParam("@userid", (DbType)SqlDbType.Int,4,userid),
					                    DbHelper.MakeInParam("@permission", (DbType)SqlDbType.NVarChar,100,permission)
                                       };
            return Convert.ToBoolean(DbHelper.ExecuteScalar(CommandType.StoredProcedure,"IsAuthorized",parameters));
        }



    }
}
